﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using MockPrjTeam2.Entity;
using MockPrjTeam2.Common;

namespace MockPrjTeam2.DAO
{
    public class BussinessDao
    {               
        public List<Bussiness> getAllBussinessByKey(string bussName, string sicCode)
        {
            const string getAllBussTypeQuery = @"SELECT BussinessType.BussinessTypeID,BussinessType.BussinessTypeName,BussinessType.SicCode
                                                FROM    BussinessType 
                                                WHERE BussinessType.BussinessTypeName  like '%'+@BussinessTypeName+'%' AND BussinessType.SicCode like '%'+@SicCode+'%'";
            List<Bussiness> listBussType = new List<Bussiness>();
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter { ParameterName = "@BussinessTypeName", Value = bussName };
                sqlParameters[1] = new SqlParameter { ParameterName = "@SicCode", Value = sicCode };

                var reader = helper.ExecuteReader(getAllBussTypeQuery, sqlParameters);
                while (reader.Read())
                {                                        
                    var type = new Bussiness(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                    listBussType.Add(type);
                }
            }
            return listBussType;
        }
        public Bussiness getById(int id)
        {
            Bussiness buss;
            const string getBussQueryById = @"SELECT BussinessTypeID, BussinessTypeName,SicCode
                                                            FROM BussinessType 
                                                            WHERE BussinessTypeID=@BussinessTypeID";
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter { ParameterName = "@BussinessTypeID", Value = id };            
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getBussQueryById, sqlParameter);
                if (reader.Read())
                {
                    buss = new Bussiness(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                }
                else
                    buss = null;
            }
            return buss;
        }
        public bool updateBussinessType(Bussiness buss)
        {
            bool reply = false;
            const string updateBussinessQuery = @"UPDATE BussinessType SET SICCode = @SICCode,
                                                                     BussinessTypeName = @BussinessTypeName                                                                 
                                            WHERE BussinessTypeID=@BussinessId";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[3];
                sqlParameters[0] = new SqlParameter { ParameterName = "@SICCode", Value = buss.SicCode };
                sqlParameters[1] = new SqlParameter { ParameterName = "@BussinessTypeName", Value = buss.BusinessName };
                sqlParameters[2] = new SqlParameter { ParameterName = "@BussinessId", Value = buss.BusinessId };

                reply = helper.ExecuteInsertQuery(updateBussinessQuery, sqlParameters);
            }
            return reply;     
        }
        public bool addBussinessType(Bussiness buss)
        {
            bool reply = false;
            const string insertBussinessQuery = @"INSERT INTO BussinessType (SICCode,BussinessTypeName) VALUES (@SICCode, @BussinessTypeName)";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter { ParameterName = "@SICCode", Value = buss.SicCode };
                sqlParameters[1] = new SqlParameter { ParameterName = "@BussinessTypeName", Value = buss.BusinessName };                                

                reply = helper.ExecuteInsertQuery(insertBussinessQuery, sqlParameters);
            }
            return reply;        
        }
    }
}